from dao.BaseDao import BaseDao
from Static import Static
import pandas as pd
from util import CommonHelper


class DeptDao(BaseDao):
    def initDB(self):
        self.__createTableDepartment()

    def __createTableDepartment(self):
        sql = """
            CREATE TABLE IF NOT EXISTS {} (
                `id` int NOT NULL AUTO_INCREMENT,
                `name` VARCHAR ( 50 ),
                `state` VARCHAR ( 20 ) DEFAULT '1',
            PRIMARY KEY ( `id` ) 
            ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;""".format(
            Static.DB_Department
        )
        self.executeNoResult(sql)

    # 查询部门列表
    def queryDepts(self):
        self.__createTableDepartment()
        sql_query = "SELECT * from {}".format(Static.DB_Department)
        df = self.readSQL(sql_query)
        return df

    # 获取特定部门信息
    def getDeptInfo(self, deptId):
        self.__createTableDepartment()
        sql_query = "SELECT * from `{}` WHERE `id` = {}".format(
            Static.DB_Department, deptId
        )
        df = self.readSQL(sql_query)
        return df

    # 获取特定部门信息
    def getDeptInfoByName(self, deptName):
        self.__createTableDepartment()
        sql_query = "SELECT * from `{}` WHERE `name` = '{}'".format(
            Static.DB_Department, deptName
        )
        df = self.readSQL(sql_query)
        return df

    # 更新部门信息
    def updateDept(self, deptId, deptName):
        self.__createTableDepartment()
        sql_query = """
            UPDATE `{}`
            SET
                `name` = '{}'
            WHERE
                `id` = {}
        """.format(
            Static.DB_Department, deptName, deptId
        )
        self.executeNoResult(sql_query)

    # 新增部门
    def addDepts(self, df):
        self.__createTableDepartment()
        self.write2sql(df, Static.DB_Department)

    # 删除部门
    def delDept(self, deptId):
        self.__createTableDepartment()
        sql_query = """
            DELETE FROM `{}` WHERE `id` = {}
        """.format(
            Static.DB_Department, deptId
        )
        self.executeNoResult(sql_query)

    # 部门状态切换
    def changeDeptState(self, deptId, state):
        self.__createTableDepartment()
        sql_query = """
            UPDATE `{}`
            SET
                state = '{}'
            WHERE
                id = {}
        """.format(
            Static.DB_Department, state, deptId
        )
        self.executeNoResult(sql_query)
